import uuid
from typing import Optional

from sqlalchemy import ForeignKey, String, Text
from sqlalchemy.dialects.postgresql import UUID
from sqlalchemy.orm import Mapped, mapped_column, relationship

from app.core.database import Base


class DoctorProfile(Base):
    __tablename__ = "doctor_profiles"

    user_id: Mapped[uuid.UUID] = mapped_column(
        UUID(as_uuid=True),
        ForeignKey("users.id", ondelete="CASCADE"),
        unique=True,
        nullable=False,
    )
    qualification_certificate_no: Mapped[Optional[str]] = mapped_column(String(64), nullable=True)
    registration_certificate_no: Mapped[Optional[str]] = mapped_column(String(64), nullable=True)
    hospital_code: Mapped[Optional[str]] = mapped_column("hospital_name", String(128), nullable=True)
    department_code: Mapped[Optional[str]] = mapped_column("department", String(64), nullable=True)
    title_code: Mapped[Optional[str]] = mapped_column("title", String(64), nullable=True)
    audit_comment: Mapped[Optional[str]] = mapped_column(Text, nullable=True)

    user = relationship("User", back_populates="doctor_profile")
    certificates = relationship(
        "DoctorCertificateFile",
        back_populates="profile",
        cascade="all, delete-orphan",
    )


class DoctorCertificateFile(Base):
    __tablename__ = "doctor_certificate_files"

    profile_id: Mapped[uuid.UUID] = mapped_column(
        UUID(as_uuid=True),
        ForeignKey("doctor_profiles.id", ondelete="CASCADE"),
        nullable=False,
    )
    file_id: Mapped[uuid.UUID] = mapped_column(
        UUID(as_uuid=True),
        ForeignKey("uploaded_files.id", ondelete="CASCADE"),
        nullable=False,
    )

    profile = relationship("DoctorProfile", back_populates="certificates")
    file = relationship("UploadedFile")
